package core.rpt;

import java.io.File;
import java.io.FileOutputStream;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.HashSet;
import java.util.LinkedHashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.jdbc.support.rowset.SqlRowSet;

import com.mysql.jdbc.StringUtils;
import com.yalu.dao.base.BaseJDBCDao;
import com.yalu.util.SpringUtil;

import core.rpt.model.DailySales;
import core.rpt.model.DailySalesSummary;
import core.rpt.model.ShopDetail;

public class DailySalesReport {
	private static final BigDecimal ONE_HUNDRED = new BigDecimal(100);
	
	public static void main(String[] args) throws Exception {
		Workbook workbook = genReport(null, null, null);
		workbook.write(new FileOutputStream("C:\\tradelink\\Exceltemplate\\DailySales1.xls"));
	}
	
	public static Workbook genReport(final String beginTime, String endTime, String shopIds) throws Exception {
		LinkedHashMap<String, DailySalesSummary> data = getReportData(beginTime, endTime, shopIds);
		Workbook tmpl = WorkbookFactory.create(new File("C:\\tradelink\\Exceltemplate\\DailySales.xls"));
		Sheet sheet = tmpl.getSheetAt(0);

		int startRow = 2;
		int rowIdx = startRow;
		BigDecimal grandGross = new BigDecimal(0);
		BigDecimal grandDiff = new BigDecimal(0);
		Map<String, ShopDetail> shopDetails = getShopDetails(data);
		Map<Integer, BigDecimal> successRates = new HashMap<Integer, BigDecimal>();
		Integer maxCol = getMaxCol(shopDetails);
		writeHeaders(sheet, shopDetails);
		
//		按照每个日期的数据来处理
		for (String payTime : data.keySet()) {
			Row row = ExcelUtil.getRow(sheet, rowIdx);
			initCellData(row, maxCol);
			rowIdx++;

//			写每一行的summary数据
			DailySalesSummary summary = data.get(payTime);
			BigDecimal amount = summary.getTotalSellPrice().add(summary.getTotalRealIncomeFreight());
			BigDecimal lyAmount = summary.getLyTotalSellPrice().add(summary.getLyTotalRealIncomeFreight());
			ExcelUtil.setCellValue(row, 0, summary.getDate());
			ExcelUtil.setCellValue(row, 1, amount, 0);
			ExcelUtil.setCellValue(row, 2, summary.getTotalPieces());
			if (summary.getTotalSellPrice().doubleValue()!=0) {
				BigDecimal grossPer =  summary.getTotalSellPrice().subtract(summary.getTotalCostPrice()).divide(summary.getTotalSellPrice(), 4, RoundingMode.HALF_UP);
				grandGross = grandGross.add(grossPer);
				ExcelUtil.setCellValue(row, 3, grossPer.multiply(ONE_HUNDRED).setScale(2).toPlainString() + "%");
			} else {
				ExcelUtil.setCellValue(row, 3, "0.00%");
			}
			ExcelUtil.setCellValue(row, 4, summary.getTotalSellPrice().subtract(summary.getTotalCostPrice()), 0);
			ExcelUtil.setCellValue(row, 5, summary.getLyTotalSellPrice().add(summary.getLyTotalRealIncomeFreight()), 0);

			if (lyAmount.doubleValue()!=0) {
				BigDecimal diff = amount.subtract(lyAmount).divide(lyAmount, 4, RoundingMode.HALF_UP);
				grandDiff = grandDiff.add(diff);
				ExcelUtil.setCellValue(row, 6, diff.multiply(ONE_HUNDRED).setScale(2).toPlainString() + "%");
			} else if (amount.doubleValue() != 0) {
				ExcelUtil.setCellValue(row, 6, "100%");
			} else {
				ExcelUtil.setCellValue(row, 6, "0");
			}
			//ExcelUtil.getCell(row, 6).setCellFormula("IF(F" + rowIdx + " = 0, \"100%\", CONCATENATE(ROUND((B" + rowIdx + "-F" + rowIdx + ")/F" + rowIdx + "*100, 2),\"%\"))");

			// 根据每一行拥有的所有店铺数据来处理
			List<DailySales> storeSales = summary.getStoreSales();
			for (DailySales sale : storeSales) {
				String attributeId = sale.getAttributeId();
				String shopId = String.valueOf(sale.getShopId());

//				从map里面得到上次某一间店铺的数据起始位置
				String key = shopId;
				if (attributeId.equals("5")) {
					key = "A:" + attributeId;
				}
				
				Integer storeCol = shopDetails.get(key).getShopColumn();

//				写下这家店铺这一天纪录
				if (attributeId.equals("1") || attributeId.equals("2")) {
					writeJingDongData(row, storeCol, sale, successRates);
				} else if (attributeId.equals("3") || attributeId.equals("4")
						 || attributeId.equals("6") || attributeId.equals("7")) {
					writeTianMaoData(row, storeCol, sale);
				} else if (attributeId.equals("5")) {
					writeShangChengData(row, storeCol, sale);
				} else if (attributeId.equals("DX")) {
					writeDaiXiaoData(row, storeCol, sale);
				}
			}
		}
		
		// Write Summary data
		String formula = ""; //毛利润
		Row row = ExcelUtil.getRow(sheet, rowIdx);
		ExcelUtil.setCellValue(row, 0, "总计");
		ExcelUtil.getCell(row, 1).setCellFormula("Sum(B3:B" + rowIdx + ")");
		ExcelUtil.getCell(row, 2).setCellFormula("Sum(C3:C" + rowIdx + ")");
//		ExcelUtil.getCell(row, 3).setCellFormula("Average(D3:D" + rowIdx + ")");
		ExcelUtil.setCellValue(row, 3, grandGross.divide(new BigDecimal(data.size()), 4, RoundingMode.HALF_UP).multiply(ONE_HUNDRED).setScale(2).toPlainString() + "%");
		ExcelUtil.getCell(row, 4).setCellFormula("Sum(E3:E" + rowIdx + ")");
		ExcelUtil.getCell(row, 5).setCellFormula("Sum(F3:F" + rowIdx + ")");
//		ExcelUtil.getCell(row, 6).setCellFormula("IF(F" + rowIdx + " = 0, \"100%\", (B" + rowIdx + "-F" + rowIdx + ")/F" + rowIdx + ")");
//		ExcelUtil.setCellValue(row, 6, grandDiff.divide(new BigDecimal(data.size()), 4, RoundingMode.HALF_UP).multiply(ONE_HUNDRED).setScale(2).toPlainString() + "%");
		// format sample : IF(F504=0,(IF(B504=0,0,1)),(B504-F504)/F504)*100&"%"
		ExcelUtil.getCell(row, 6).setCellFormula("TEXT(ROUND(IF(F" + (rowIdx + 1) + "=0,(IF(B" + (rowIdx + 1) + "=0,0,1)),(B" + (rowIdx + 1) + "-F" + (rowIdx + 1) + ")/F" + (rowIdx + 1) + ")*100, 2), \"0.00\")&\"%\"");

		for (String key : shopDetails.keySet()) {
			ShopDetail shop = shopDetails.get(key);

			Integer pos = shop.getShopColumn();
			Integer colCount = ShopDetail.SHOP_COL_COUNT.get(shop.getAttributeId());
			if (colCount != 2) { // 非代销
				formula += ExcelUtil.getColumnAlphNameByIndex(pos + 2) + "#idx#+";
			}

			switch (colCount) {
				case 8:
					createSummaryField(row, rowIdx, pos + 3); // views
					createSummaryField(row, rowIdx, pos + 4); // guests
//					createSummaryField(row, rowIdx, pos + 5, "average");
					ExcelUtil.setCellValue(row, pos + 5, successRates.get(pos + 5).divide(new BigDecimal(data.size()), 2, RoundingMode.HALF_UP).toPlainString() + "%");
					createSummaryField(row, rowIdx, pos + 6, "average");

					ExcelUtil.getCell(row, pos + 7).setCellFormula("if(" +
						ExcelUtil.getColumnAlphNameByIndex(pos + 1) + (rowIdx + 1) +
						"=0, 0, round(" +
						ExcelUtil.getColumnAlphNameByIndex(pos) + (rowIdx + 1) + "/" +
						ExcelUtil.getColumnAlphNameByIndex(pos + 1) + (rowIdx + 1) + ", 2))"); //客单价
					createSummaryField(row, rowIdx, pos + 2); // gross revenue
					createSummaryField(row, rowIdx, pos); // amount
					createSummaryField(row, rowIdx, pos + 1); // pieces		
					break;
				case 4:
					createSummaryField(row, rowIdx, pos + 2); // gross revenue
					createSummaryField(row, rowIdx, pos); // amount
					createSummaryField(row, rowIdx, pos + 1); // pieces
					ExcelUtil.getCell(row, pos + 3).setCellFormula("if(" +
							ExcelUtil.getColumnAlphNameByIndex(pos + 1) + (rowIdx + 1) +
							"=0, 0, round(" + 
							ExcelUtil.getColumnAlphNameByIndex(pos) + (rowIdx + 1) + "/" +
							ExcelUtil.getColumnAlphNameByIndex(pos + 1) + (rowIdx + 1) + ", 2))");
					break;
				case 2:
					createSummaryField(row, rowIdx, pos); // amount
					createSummaryField(row, rowIdx, pos + 1); // pieces
					break;
			}
		}
		
		//每一行的毛利润
		formula = formula.substring(0, formula.length() - 1);
		for (int i = 2; i < rowIdx; i++) {
			row = ExcelUtil.getRow(sheet, i);
			ExcelUtil.getCell(row, 4).setCellFormula(formula.replaceAll("#idx#", String.valueOf(i + 1)));
		}
		
		return tmpl;
	}
	
	private static Integer getMaxCol(Map<String, ShopDetail> shopDetails) {
		ShopDetail maxColShop = null;
		for (ShopDetail detail : shopDetails.values()) {
			if (maxColShop == null || detail.getShopColumn() > maxColShop.getShopColumn()) {
				maxColShop = detail;
			}
		}
		return maxColShop.getShopColumn() + ShopDetail.SHOP_COL_COUNT.get(maxColShop.getAttributeId());
	}
	
	private static void initCellData(Row row, Integer maxCol) {
		for (int i = 7; i < maxCol; i++) {
			ExcelUtil.setCellValue(row, i, 0L);
		}
	}

	private static Map<String, ShopDetail> getShopDetails(LinkedHashMap<String, DailySalesSummary> data) {
		int startCol = 7;
		int currStoreCol = startCol;
		Map<String, ShopDetail> shopDetails = new HashMap<String, ShopDetail>();

		Set<ShopDetail> shops = new HashSet<ShopDetail>();
		for (DailySalesSummary summary : data.values()) {
			shops.addAll(summary.getStores());
		}
		List<ShopDetail> sortedShops = new ArrayList<ShopDetail>();
		sortedShops.addAll(shops);

		Collections.sort(sortedShops);

		for (ShopDetail s : sortedShops) {
			String attributeId = s.getAttributeId();
			String shopId = String.valueOf(s.getShopId());

			String key = shopId;
			if (attributeId.equals("5")) {
				key = "A:" + attributeId;
			}

			s.setShopColumn(currStoreCol);
			if (!shopDetails.containsKey(key)) {
				shopDetails.put(key, s);
	
				currStoreCol += ShopDetail.SHOP_COL_COUNT.get(attributeId);
			}
		}

		return shopDetails;
	}
	
	private static void writeHeaders(Sheet sheet, Map<String, ShopDetail> shopDetails) {
		// Write shop name
		int headerRow = 0;
		Row hdrRow = sheet.getRow(headerRow);
		
		for (String key : shopDetails.keySet()) {
			ShopDetail shop = shopDetails.get(key);
			String attributeId = shop.getAttributeId();
			String name = shop.getShopName();
			Integer storeCol = shop.getShopColumn();
			if (shop.getAttributeId().equals("5")) {
				name = "商城分销";
			}

			ExcelUtil.setCellValue(hdrRow, storeCol, name);
			sheet.addMergedRegion(new CellRangeAddress(
					headerRow, headerRow, storeCol, storeCol + ShopDetail.SHOP_COL_COUNT.get(attributeId) - 1));

			// Write each figure header
			Row titleRow = sheet.getRow(headerRow + 1);
			if (attributeId.equals("1") || attributeId.equals("2")) {
				writeJingDongHeader(titleRow, storeCol);
			} else if (attributeId.equals("3") || attributeId.equals("4")
					 || attributeId.equals("5") || attributeId.equals("6")
					 || attributeId.equals("7")) {
				writeTianMaoShangChengHeader(titleRow, storeCol);
			} else if (attributeId.equals("DX")) {
				writeDaiXiaoHeader(titleRow, storeCol);
			}
		}
	}

	private static void createSummaryField(final Row row, final int rowIdx, final int pos) {
		createSummaryField(row, rowIdx, pos, "Sum");
	}
	
	private static void createSummaryField(final Row row, final int rowIdx, final int pos, final String formula) {
		String posName = ExcelUtil.getColumnAlphNameByIndex(pos);
		ExcelUtil.getCell(row, pos).setCellFormula("round(" + formula + "(" + posName + "3:" + posName + rowIdx + "), 2)");		
	}
	
	private static void writeJingDongHeader(Row hdrRow, int storeCol) {
		ExcelUtil.setCellValue(hdrRow, storeCol, "销售金额");
		ExcelUtil.setCellValue(hdrRow, storeCol + 1, "件数");
		ExcelUtil.setCellValue(hdrRow, storeCol + 2, "毛利润");
		ExcelUtil.setCellValue(hdrRow, storeCol + 3, "浏览量");
		ExcelUtil.setCellValue(hdrRow, storeCol + 4, "访客数");
		ExcelUtil.setCellValue(hdrRow, storeCol + 5, "转化率");
		ExcelUtil.setCellValue(hdrRow, storeCol + 6, "平均每单件数");
		ExcelUtil.setCellValue(hdrRow, storeCol + 7, "客单价");
	}
	
	private static void writeTianMaoShangChengHeader(Row hdrRow, int storeCol) {
		ExcelUtil.setCellValue(hdrRow, storeCol, "销售金额");
		ExcelUtil.setCellValue(hdrRow, storeCol + 1, "件数");
		ExcelUtil.setCellValue(hdrRow, storeCol + 2, "毛利润");
		ExcelUtil.setCellValue(hdrRow, storeCol + 3, "平均每件金额");
	}
	
	private static void writeDaiXiaoHeader(Row hdrRow, int storeCol) {
		ExcelUtil.setCellValue(hdrRow, storeCol, "销售金额");
		ExcelUtil.setCellValue(hdrRow, storeCol + 1, "件数");
	}
	
	private static void writeJingDongData(Row row, int storeCol, DailySales sale, Map<Integer, BigDecimal> successRates) {
		ExcelUtil.setCellValue(row, storeCol, sale.getAmount(), 0);
		ExcelUtil.setCellValue(row, storeCol + 1, sale.getTotalPieces());
		ExcelUtil.setCellValue(row, storeCol + 2, sale.getGrossRevenue(), 0);
		ExcelUtil.setCellValue(row, storeCol + 3, sale.getViews());
		ExcelUtil.setCellValue(row, storeCol + 4, sale.getGuests(), 0);

		BigDecimal successRate = BigDecimal.ZERO;
		Integer col = storeCol + 5;
		if (sale.getSuccessRate() != null) {
			successRate = sale.getSuccessRate().multiply(ONE_HUNDRED);
			ExcelUtil.setCellValue(row, col, successRate.setScale(2).toPlainString() + "%");
		} else {
			ExcelUtil.setCellValue(row, col, "0.00%");
		}
		if (successRates.containsKey(col)) {
			successRates.put(col, successRates.get(col).add(successRate));
		} else {
			successRates.put(col, successRate);
		}

		ExcelUtil.setCellValue(row, storeCol + 6, sale.getItemPerTx(), 0);
		ExcelUtil.setCellValue(row, storeCol + 7, sale.getAvgCost(), 2);
		return;
	}
	
	private static void writeTianMaoData(Row row, int storeCol, DailySales sale) {
		ExcelUtil.setCellValue(row, storeCol, sale.getAmount(), 0);
		ExcelUtil.setCellValue(row, storeCol + 1, sale.getTotalPieces());
		ExcelUtil.setCellValue(row, storeCol + 2, sale.getGrossRevenue(), 0);
		if (sale.getAmount() != null) {
			ExcelUtil.setCellValue(row, storeCol + 3, sale.getAmount().divide(new BigDecimal(sale.getTotalPieces()), 2, RoundingMode.HALF_UP), 2);
		} else {
			ExcelUtil.setCellValue(row, storeCol + 3, null, 2);
		}
	}
	
	private static void writeShangChengData(Row row, int storeCol, DailySales sale) {
		BigDecimal amount = sale.getAmount();
		Long pieces = sale.getTotalPieces();
		if (amount == null) {
			amount = new BigDecimal(0);
		}

		try {
			amount = new BigDecimal(ExcelUtil.getCellValue(row, storeCol, false)).add(amount);
			pieces = new Long(ExcelUtil.getCellValue(row, storeCol + 1, false)) + pieces;
		} catch (NumberFormatException e) {
		}
		ExcelUtil.setCellValue(row, storeCol, amount, 0);
		ExcelUtil.setCellValue(row, storeCol + 1, pieces);
		ExcelUtil.setCellValue(row, storeCol + 2, sale.getGrossRevenue(), 0);
		ExcelUtil.setCellValue(row, storeCol + 3, amount.divide(new BigDecimal(pieces), 2, RoundingMode.HALF_UP), 2);
	}
	
	private static void writeDaiXiaoData(Row row, int storeCol, DailySales sale) {
		ExcelUtil.setCellValue(row, storeCol, sale.getAmount(), 0);
		ExcelUtil.setCellValue(row, storeCol + 1, sale.getTotalPieces());
	}

	/**
	 * 数据结构：key是日期，一个summary对象对应报表里面一行数据。所以，一个summary里面有一个list的DailySales对象
	 * @param beginTime
	 * @param endTime
	 * @param shopId
	 * @return
	 * @throws Exception
	 */
	private static LinkedHashMap<String, DailySalesSummary> getReportData(final String beginTime, String endTime, String shopIds) throws Exception {
		LinkedHashMap<String, DailySalesSummary> rows =
			new LinkedHashMap<String, DailySalesSummary>();
		BaseJDBCDao baseJDBCDao = (BaseJDBCDao) SpringUtil
		.getBean("baseJDBCDaoImpl");
			
			String sql = "select pay_time, attribute_id, shopid, shop_name, "
				+ " total_sell_price, total_real_income_freight, "
				+ " total_pieces, total_cost_price, amount, "
				+ " gross_per, gross_revenue, "
				+ " views, guests, success_rate, avg_cost, pieces_per_tx, "
				+ " total_sell_price_ly, total_real_income_freight_ly "
				+ " from ("
				+ "select d.pay_time, d.attribute_id, d.shopid, d.shop_name, "
				+ " d.total_sell_price, d.total_real_income_freight, "
				+ " d.total_pieces, d.total_cost_price, d.amount, "
				+ " d.gross_per, d.gross_revenue, "
				+ " views, guests, success_rate, avg_cost, "
				+ " total_pieces / no_of_tx pieces_per_tx, "
				+ " total_sell_price_ly, total_real_income_freight_ly "
				+ " from v_daily_sales d left join v_itemdata on shopid = no and pay_time = data_date "
				+ "      left join v_daily_sales_ly l on d.shopid = l.shopid and d.pay_time = l.pay_time"
				+ " where d.pay_time is not null 1 = 1 "
//				+ " and d.pay_time >= '2013-07-1' and d.pay_time <= '2013-07-06'"
				+ " union "
				+ "select data_date pay_time, i.attribute_id, no shopid, i.shop_name, "
				+ " total_sell_price, total_real_income_freight, "
				+ " total_pieces, total_cost_price, amount, "
				+ " gross_per, gross_revenue, "
				+ " views, guests, success_rate, avg_cost, "
				+ " total_pieces / no_of_tx pieces_per_tx, "
				+ " total_sell_price_ly, total_real_income_freight_ly "
				+ " from v_itemdata i left join v_daily_sales on no = shopid and data_date = pay_time "
				+ "      left join v_daily_sales_ly l on i.no = l.shopid and i.data_date = l.pay_time"
				+ " where data_date is not null 2 = 2"
//				+ " and data_date >= '2013-07-01' and data_date <= '2013-07-06'"
				+ " ) A order by pay_time, shopid";
			String criteria = getCriteria("d.pay_time", "d.shopid", beginTime, endTime, shopIds);
			sql = sql.replace(" 1 = 1", criteria);
			
			criteria = getCriteria("data_date", "no", beginTime, endTime, shopIds);
			sql = sql.replace(" 2 = 2", criteria);

			System.out.println(sql);
			SqlRowSet rs = baseJDBCDao.executeQuery(sql);
			
			while (rs.next()) {
				String payTime = rs.getString("pay_time");
				DailySalesSummary data = rows.get(payTime);
				if (data == null) {
					data = new DailySalesSummary();
					data.setStoreSales(new LinkedList<DailySales>());
					rows.put(payTime, data);
				}

				DailySales sales = new DailySales();
				sales.setDate(rs.getString("pay_time"));
				sales.setAttributeId(rs.getString("attribute_id"));
				sales.setShopId(rs.getInt("shopid"));
				sales.setShopName(rs.getString("shop_name"));
				sales.setTotalSellPrice(rs.getBigDecimal("total_sell_price"));
				sales.setTotalRealIncomeFreight(rs.getBigDecimal("total_real_income_freight"));
				sales.setLyTotalSellPrice(rs.getBigDecimal("total_sell_price_ly"));
				sales.setLyTotalRealIncomeFreight(rs.getBigDecimal("total_real_income_freight_ly"));
				sales.setTotalPieces(rs.getLong("total_pieces"));
				sales.setTotalCostPrice(rs.getBigDecimal("total_cost_price"));
				sales.setAmount(rs.getBigDecimal("amount"));
				sales.setGrossPer(rs.getBigDecimal("gross_per"));
				sales.setGrossRevenue(rs.getBigDecimal("gross_revenue"));
				sales.setViews(rs.getLong("views"));
				sales.setGuests(rs.getBigDecimal("guests"));
				sales.setSuccessRate(rs.getBigDecimal("success_rate"));
				sales.setAvgCost(rs.getBigDecimal("avg_cost"));
				sales.setItemPerTx(rs.getBigDecimal("pieces_per_tx"));

				data.addStoreSales(sales);
			}
		return rows;
	}

	private static String getCriteria(String timeCol, String idCol, String beginTime, String endTime, String shopIds) {
		String criteria = "";
		if (!StringUtils.isNullOrEmpty(beginTime)) {
			criteria += " and " + timeCol + " >= '" + beginTime.replace("/", "-") + "'";
		}
		if (!StringUtils.isNullOrEmpty(endTime)) {
			criteria += " and " + timeCol + " <= '" + endTime.replace("/", "-") + "'";
		}
		if (!StringUtils.isNullOrEmpty(shopIds)) {
			String[] ids = shopIds.split(",");
			criteria += " and " + idCol + " in (";
			for (String id : ids) {
				criteria += id + ", ";
			}
			criteria = criteria.substring(0, criteria.length() - 2);
			criteria += ")";
		}
		return criteria;
	}
}
